package com.swordget.dao.impl;

import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.fastjson.JSONArray;
import com.swordget.commons.JdbcUtil;
import com.swordget.dao.DataHandlerDao;
import com.swordget.entity.ColumnDesign;
import com.swordget.entity.po.DataQueryPo;
import org.springframework.beans.factory.annotation.Autowired;

import java.io.*;
import java.sql.*;
import java.util.Arrays;
import java.util.HashSet;
import java.util.List;
import java.util.Set;

/**
 * @Author: David
 * @Date: Created in 18:11 2020/4/8
 * @Description: 数据查询服务 dao 层mysql实现
 * @Modified By:
 * @Version: 0.0.1
 */
public class DataHandlerMysqlDaoImp extends DataHandlerOracleDaoImp implements DataHandlerDao {

    @Autowired
    private DruidDataSource dataSource;

    @Autowired
    private JdbcUtil jdbcUtil;


    @Override
    public DataQueryPo query(int rows, int page, JSONArray params, String tableName, List<String> columns,
                             List<String> types, List<ColumnDesign> columnDesigns, String dataSourceId) {
        //查询数据
        final String queryColumn = String.join(",", columns);
        final String paramSql = handlerParams(params);
        String querySql = String.format("SELECT %s FROM %s WHERE %s ORDER BY CREATEDATE DESC, CREATETIME DESC limit %d,%d", queryColumn, tableName,
                paramSql, (page - 1) * rows, (page * rows));
        String countSql = String.format("SELECT COUNT(*) FROM %s WHERE %s", tableName, paramSql);
        return DataQueryPo.builder()
                .data(this.executeQuery(querySql, columns, types, columnDesigns, ""))
                .total(jdbcUtil.executeCount(countSql))
                .build();
    }

    @Override
    public List<List<String>> queryByList(int rows, int page, JSONArray queryParam, String tableName,
                                          List<String> columns, List<String> types) {
        //查询数据
        final String queryColumn = String.join(",", columns);
        final String paramSql = handlerParams(queryParam, null);
        String querySql = String.format("SELECT %s FROM %s WHERE %s ORDER BY CREATEDATE DESC, CREATETIME DESC limit %d,%d", queryColumn, tableName,
                paramSql, (page - 1) * rows, (page * rows));

        return this.executeQueryByList(querySql, columns, types, null,"");
    }


    @Override
    public int insert(File loadData, String tableName, List<String> columns) throws Exception {
        String filePath = loadData.getAbsolutePath();
        filePath = filePath.replaceAll("\\\\", "/");
        String command = String.format("load data local infile '%s' into table %s " +
                "CHARACTER SET utf8 " +
                "FIELDS TERMINATED BY ','" +
                "ESCAPED BY '\\\\' " +
                "LINES TERMINATED BY '\\n' " +
                "(%s)", filePath, tableName, String.join(",", columns));
        try (Connection conn = dataSource.getConnection();
             Statement statement = conn.createStatement();
        ) {
            int result = statement.executeUpdate(command);
            if (result < 99999) {
                throw new RuntimeException("不正确");
            }
            return result;
        }
    }

    @Override
    public boolean copyData(String tableName, String tableNameTmp, List<String> columns, String importId) {
        String column = String.join(",", columns);
        String copyCommand = String.format("INSERT INTO %s(%s) SELECT %s FROM %s WHERE IMPORTID = '%s'",
                tableName, column, column, tableNameTmp, importId);
        //String deleteCommand = String.format("DELETE FROM %s WHERE IMPORTID = '%s'", tableNameTmp, importId);
        return jdbcUtil.executeBatch(copyCommand);
    }

    @Override
    public List<List<String>> queryByListByStatus(int rows, int page, JSONArray queryParam, String tableName,
                                                  List<String> columns, List<String> types, List<ColumnDesign> columnDesigns,
                                                  String[] serials, String dataSourceId) {
        //查询数据
        final String queryColumn = String.join(",", columns);
        final String paramSql = this.handlerParams(queryParam, serials);
        String querySql = String.format("SELECT * FROM (" +
                        "SELECT %s FROM (" +
                        "SELECT %s FROM %s WHERE %s ORDER BY CREATEDATE, CREATETIME DESC) t limit %d,%d" +
                        ")tt", queryColumn, queryColumn, tableName, paramSql,
                (page - 1) * rows, rows);
        return this.executeQueryByList(querySql, columns, types, null,"");
    }

}
